#!/usr/bin/env python3
# -*- coding:utf-8 -*-
#
from common.handleDb import HandleMysql

def sync():
  # 数据的数据连接
  sdb = HandleMysql("source-database")
  tdb = HandleMysql("target-database")
  tlog = HandleMysql("target-database")
  #获取迁移表的SQL
  tables = tlog.search("select * from data_sync")
  for table in tables:
    # 查询SQL语句
    querysql = table['query_sql']
    cursor = sdb.query_cursor(querysql)
    # 获取第一条记录
    record = cursor.fetchone()
    if record is None:
      continue
    count = 1
    values = []
    for key in record.keys():
      values.append('%s')
    replaceSql = "replace into " + table['target_table'] + "(`"+ "`,`".join(record.keys()) +"`) values ("+",".join(values)+")"
    # 开始记录
    tlog.execute_sql("update data_sync set `sync_start_at`=now() ,`sync_result`=%s where id=%s" ,["开始导入",table['id']])
    print('开始导入...')
    datas = []
    while not(record is None):
      datas.append(tuple(record.values()))
      if (count % 500 == 0):
        tdb.execute_many(replaceSql,datas)
        tlog.execute_sql("update data_sync set `sync_result`=%s where id=%s", ["已经导入"+str(count)+"行",table['id']])
        print('已经导入...'+str(count)+"行")
        datas = []
      record = cursor.fetchone()
      if (record is None):
        if len(datas) > 0:
          tdb.execute_many(replaceSql,datas)
        # 结束记录
        tlog.execute_sql("update data_sync set `sync_end_at`=now(),`sync_result`=%s where id=%s", ["成功导入"+str(count)+"行",table['id']])
        print('成功导入'+str(count)+"行")
      count += 1
  #关闭数据库
  tlog.close_mysql()
  sdb.close_mysql()
  tdb.close_mysql()

if __name__ == '__main__':
  sync()